/** JDBCStatements: Demonstrates the various different types of statements in JDBC.

   Retrieval and updating of information in the database are done through SQL statements.
The SQL statements can be called:

	-- query/retrieval statement, the statements used to to get infornation from DB, and
	-- Non-query/DML, the data manipulation statements such as INSERT, DELETE, UPDATE,
	   CREATE and etc.

    A single SQL statemenet without any paramter can be created dynamically meaning that
the statement is composed in application, parsed and executed Those statements are called
dynamic SQL statements.

    A single statement with possibly input parameter(s) can be prepared meaning that it is
composed with input parameters, and parsed once, and will be executed more than once with
different set of parameters. The prepared statements have better performance than dynamic
statements.

   Multiple SQL statements can be put into a function or procedure and stored inside database
management system. Those functions or procedures are composed, parsed, tested and stored inside
database so that any application can call those procedures and function for the best performance.

   The Connection class of java.sql provide three different methods for developers to create
each of the three types of statement. The three methods of Connection class are:

	-- Statement createStatement() throws SQLException
		The returned Statement object allows you to execute no-argument SQL (query and
		non-query) statements.
	-- PreparedStatement prepareStatement( String sql ) throws SQLException
		The method creates PreparedStatement object that can be used to set paramenters
		and executed the query with different parameters. The parameters in sql is
		indicated by '?'.
	-- CallableStatement prepareCall( String call ) throws SQLException
		The method allows you make a call to stored procedure. The stored procedure can
		have any number of IN and OUT parameters. The CallableStatement class has methods
		for setting and getting parameters before and after call. The syntax of calling
		a stored procedure:
			{call procedure_name (?, ?, ..., ?) }
			{? = call function_name(?, ?, ..., ?) }

		Also you can call a Oracle anonymous block.

    For each of three type of Statement interface, JDBC driver provider will implement the
methods in those interfaces. The basic methods in each interface are given below:

   1. Statement: created by cnn.createStatement() of Connectoin object. The Statement object
	can then be used to excute a query that has no IN/OUT parameter and that returns no, single
	or multiple resultsets. The following are methods of Statement generating ResultSet.

	-- ResultSet executeQuery(String sql) of Statemenet should be used to execute
	   a query with single result set.
	-- boolean execute(String sql) is used to execute query with muliple result sets.
	   The following methods used to process each of result sets:
	    --- ResultSet getResultSet(): get current result set.
	    --- boolean getMoreResults() move to next result set.
	-- int executeUpdate(String sql): execute a non-query statement, DELETE, UPDATE, INSERT sql.

	There are many other functions in the Statement interface.

   2. PreparedStatement: subinterface of Statement. Objects of PreparedStatement are created by
	Connection's prepareStatment(String sql). The sql statement in prepareStatement() of connection
	object can have one or more input parameters represented by '?'. The sql in the prepared
	statement is parsed once, and can be executed any time with the same or different parameter.
	There is a set of methods in PreparedStatement that can be used to set the parameters of different
	types before next execution of the sql.

   3. CallableSatement: subinterface of PreparedStatement. The objects of CallableStatement are created
	by PrepareCall(Sting sql) of connection objects. The sql statement of CallableStatement can have
	any number of input and output parameters. The interface add a set of get methods to get output
	parameters from the CallableStatement objects. The input parameter setting methods are inherited
	from PreparedStatement.

  The program will demonstrate how to use PreparedStatement and CallableStatement.
*/

import java.awt.*;
import javax.swing.*;
import java.awt.event.*;
import java.sql.*;
import java.util.*;

public class JDBCStatements extends CloseableJFrame implements ActionListener {
	//------ JDBC Objects ----------------
	Connection cnn;
	Statement  stmt;
	ResultSet  res;

	//------ GUI components --------------
	Container c;
	JLabel	lbSalary = new JLabel("Display employees with Salary >= ", SwingConstants.RIGHT);
	JTextField	txtSalary = new JTextField("1000", 8);
	JButton	btnAdd    = new JButton("Add");
	JButton	btnDelete = new JButton("Delete");
	JPanel	pnSouth   = new JPanel();
	JTable	grid	    = new JTable(20, 6);

	public JDBCStatements() {
		c = getContentPane();
		c.setLayout(new BorderLayout());
		c.add(new JScrollPane(grid), BorderLayout.CENTER);
		pnSouth.add(lbSalary); pnSouth.add(txtSalary);
		btnAdd.addActionListener(this); btnDelete.addActionListener(this);
		pnSouth.add(btnAdd); pnSouth.add(btnDelete);
		c.add(pnSouth, BorderLayout.SOUTH);
		setTitle("Demo of PreparedStatement and CallableStatement");
		setSize(600, 400); show();
	}
	public void actionPerformed(ActionEvent e) {
		Object o = e.getSource();
		if ( o == btnAdd )
			new GUIEmployee("Add New Emp", cnn);
		else
			new GUIEmployee("Delete Emp", cnn, 1);
	}

	public static void main(String argv[]) { new JDBCStatements() ; }
}

class GUIEmployee extends JFrame implements ActionListener {

	static byte FIELDCOUNT = 8;
	int    fieldsShown;
	//---- GUI Components of Employee --------
	Container c;
	JLabel	lbEmployee[] = new JLabel[FIELDCOUNT];
	String	strEmployeeFields[] = {"Employee ID", "Name", "Job Title", "Manager ID", "Date Hired",
							"Salary", "Commission", "Dept No" };
	String	defaultValues[] = {"1234", "Jone", "Sales", "7876", "1988-9-1",
							"42000", "400", "10" };
	JTextField	txtEmployee[] = new JTextField[FIELDCOUNT];
	JButton	btnExecute = new JButton("Excute"), btnCancel = new JButton("Cancel");

	//---- Database related Objects ----------
	Connection cnn ;

	public GUIEmployee(String title, Connection con ) { this (title, con, FIELDCOUNT); }

	public GUIEmployee(String title, Connection con, int fieldToDisplay ) {
		cnn = con; fieldsShown = fieldToDisplay;
		c = getContentPane();
		c.setLayout(new GridLayout(fieldsShown + 1, 2));
		for ( int i = 0; i < fieldToDisplay; i ++ ) {
			lbEmployee[i] = new JLabel(strEmployeeFields[i] /*, SwingConstants.RIGHT*/);
			c.add(lbEmployee[i]);
			txtEmployee[i] = new JTextField(defaultValues[i], 10);
			c.add(txtEmployee[i]);
		}
		btnExecute.addActionListener(this); btnCancel.addActionListener(this);
		c.add(btnCancel); c.add(btnExecute);
		setTitle("New Employee Info");
		pack(); show();

		addWindowListener( new WindowAdapter() {
			public void windowClosing( WindowEvent e ) { hide(); } } );
	}

	public void actionPerformed(ActionEvent e) {
		Object o = e.getSource();
		if ( o == btnCancel ) hide();
		else if (fieldsShown == 1)
				JOptionPane.showMessageDialog(this, "Delete will be implmeneted by executeUpdate().", "Deleting Employee", JOptionPane.INFORMATION_MESSAGE);
			else
				JOptionPane.showMessageDialog(this, "Save will be implmeneted by calling a stored procedure.", "Saving New Employee", JOptionPane.INFORMATION_MESSAGE);
	}

	protected void finalize() { cnn = null; }
}